-- =============================================
-- This stored procedure looks for contentID based on the URL provided.
-- Used for "Surf to Edit" functionality.
--
-- Modifiations
-- 07/08/2003   E.Tatsui    Created
-- =============================================

CREATE              PROCEDURE amsp_CMFindContentIDtoEdit
    @InURL varchar(500),
  @OutContentID numeric OUTPUT,
  @OutEditable bit OUTPUT
AS
BEGIN

  DECLARE
    @HTMLContentID numeric,
    @ContentID numeric,
    @GECodePath varchar(255),
    @Rowcount numeric,
    @StartIndex smallint,
    @EndIndex smallint,
    @URLContentID int,
    @URLIDLen int

  SET @OutEditable = 0
  SET @Rowcount = 0

  SET @StartIndex = CHARINDEX ('&CONTENTID=', @InURL)

  -- If there is ContactID in URL, use that instead of trying to figure out
  IF @StartIndex > 0 BEGIN

    SELECT @EndIndex = CHARINDEX ('&', @InURL, @StartIndex + 1)
  
    IF @EndIndex = 0
      SET @EndIndex = LEN(@InURL) + 1
  
    SET @URLIDLen = @EndIndex - @StartIndex - 11
    SET @URLContentID = SUBSTRING(@InURL,@StartIndex+11,@URLIDLen)
  
    SELECT @ContentID = a.ContentID,
           @HTMLContentID = b.ContentID
      FROM Content a LEFT OUTER JOIN Content_HTML b
        ON a.ContentID = b.ContentID
     WHERE a.ContentID = b.ContentID
       AND a.ContentID = @URLContentID
       AND a.WorkflowStatusCode = 'P'

    SET @Rowcount = @@Rowcount
  END
  -- If we didn't find ContentID from URL or it wasn't a valid ContentID.
  IF @@Rowcount = 0 BEGIN
    SELECT @GECodePath = Value
      FROM System_Variable
     WHERE Name = 'GECodePath'

    IF Len(@GECodePath) > 1
      SET @GECodePath = RIGHT(@GECodePath,Len(@GECodePath)-1)
    ELSE IF @GECodePath = '/'
      SET @GECodePath = ''
  
    -- First match with content record.
    SELECT @ContentID = a.ContentID,
           @HTMLContentID = b.ContentID
      FROM Content a LEFT OUTER JOIN Content_HTML b
        ON a.ContentID = b.ContentID, Nav_Menu c, Website d
     WHERE a.NavMenuID = c.NavMenuID
       AND c.WebsiteKey = d.WebsiteKey
       AND a.WorkflowStatusCode = 'P'
       AND @InURL = CASE WHEN a.SecureFlag = 'Y' THEN d.SecureWebsiteRootURL
                         ELSE d.WebsiteRootURL END
                    + a.PostFuseURL
    
    SET @Rowcount = @@Rowcount
    
    -- If there wasn't a match with content record, try nav_menu.
    IF @Rowcount = 0   BEGIN
  
      SELECT @ContentID = a.ContentID,
             @HTMLContentID = b.ContentID
        FROM Content a LEFT OUTER JOIN Content_HTML b
          ON a.ContentID = b.ContentID, Nav_Menu c, Website d
       WHERE a.NavMenuID = c.NavMenuID
         AND c.ContentID = a.ContentID
         AND c.WebsiteKey = d.WebsiteKey
         AND a.WorkflowStatusCode = 'P'
         AND (@InURL = CASE WHEN a.SecureFlag = 'Y' THEN d.SecureWebsiteRootURL
                           ELSE d.WebsiteRootURL END
                       + c.PostFuseURL
           OR @InURL = CASE WHEN a.SecureFlag = 'Y' THEN d.SecureWebsiteRootURL
                           ELSE d.WebsiteRootURL END
                       + CASE WHEN c.NavContentGroupInd = 'C'
                              THEN REPLACE(c.PublishedDirectory, REPLACE(IsNull(d.ContentFolderPublishDirectory,''),'\','/'), IsNull(d.FolderContentDisplayPath,''))
                              ELSE REPLACE(c.PublishedDirectory, REPLACE(IsNull(d.NavPublishDirectory,''),'\','/'), IsNull(d.NavContentDisplayPath,''))
                              END)
      SET @Rowcount = @@Rowcount
    END
  
  END -- Ends if @@Rowcount = 0

  -- If there is a record in Content_HTML, we can redirect the user to Ektron page.
  IF @Rowcount > 0 AND @HTMLContentID IS NOT NULL BEGIN
    SET @OutContentID = @ContentID
    SET @OutEditable = 1
    print 'here'
  END

-- If there is a content record, use it.
  ELSE IF @Rowcount > 0 AND @ContentID IS NOT NULL
    SET @OutContentID = @ContentID
  ELSE IF @Rowcount = 0 BEGIN  
    -- If not, look for content_file.
    SELECT @ContentID = a.ContentID
      FROM Content_File a, Content b, Nav_Menu c, Website d
     WHERE a.ContentID = b.ContentID
       AND b.NavMenuID = c.NavMenuID
       AND c.WebsiteKey = d.WebsiteKey
       AND b.WorkflowStatusCode = 'P'
       AND @InURL =
                    CASE WHEN b.SecureFlag = 'Y' THEN d.SecureWebsiteRootURL
                         ELSE d.WebsiteRootURL END
                    + CASE WHEN c.NavContentGroupInd = 'C'
                           THEN REPLACE(c.PublishedDirectory, REPLACE(IsNull(d.ContentFolderPublishDirectory,''),'\','/'), IsNull(d.FolderContentDisplayPath,''))
                           ELSE REPLACE(c.PublishedDirectory, REPLACE(IsNull(d.NavPublishDirectory,''),'\','/'), IsNull(d.NavContentDisplayPath,''))
                           END + a.FileName

    IF @Rowcount > 0
      SET @OutContentID = @ContentID
  END

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMFindContentIDtoEdit] TO [IMIS]
GO